--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_gam_stdlig;

CREATE TABLE se_gam_stdlig AS (SELECT * FROM &erp.gam_stdlig WHERE 1=2);


--SELECT * FROM UV_PENT_GAMMES_emploi e  /*gamme standard entete*/
--SELECT * FROM UV_PDET_GAMMES d  /*gamme standard ligne*/
--SELECT * FROM  UV_PPT_GAMMES  /*gamme entete article*/
--SELECT * FROM UV_PPT_GAMMES_DETGAM where id like '%P10832741094%' ORDER BY 1/*gamme ligne article */



 INSERT INTO se_gam_stdlig

    (SELECT Rank() OVER (PARTITION BY ge.id ORDER BY ordre+0 ) codphase_no,
            'PHASE_SANS' prfregphase_cod,
            'BT_SANS' prfregbt_cod,
            ge.id stdent_cod,
            '0' stdent_ind,
            gl.operation opera_cod,
            Decode(ope.CRITERE_UO,'M',gl.operation,NULL) operamod_cod,
            Decode(ope.CRITERE_UO,'M','+',NULL) modulation_val,
            'CH' prepa_untps_cod,
            NULL outil_cod,
            'SANS' prfrprbt_cod,
            'SANS' prfrprphase_cod,
            0 enreg_id,
            'N' enreg_s,
            Rank() OVER (PARTITION BY ge.id ORDER BY ordre+0 ) stdlig_rang,
            To_Date('31/12/1967','dd/mm/yyyy')+Nvl(ge.datcre,1) deb_dat,
            NULL fin_dat,
            NULL prepamach_tps,
            NULL reamach_val,
            1 tps_coef,
            'N' editcomment_ok,
            'N' entrestock_ok,
            gl.nbrbon bons_nb
        FROM UV_PENT_GAMMES ge,UV_PDET_GAMMES gl,uv_poperations ope
        WHERE ge.id=gl.nogamme
          AND ope.id(+)=gl.operation
          AND EXISTS (SELECT 1 FROM UV_PDET_GAMMES std_lig, UV_PPT_GAMMES_DETGAM art_lig
                      WHERE std_lig.nogamme=ge.id AND art_lig.detgam=std_lig.id)
        ) ;


 UPDATE se_gam_stdlig tf
 SET (prfregphase_cod,prfregbt_cod,prfrprbt_cod,prfrprphase_cod)=(select prfregphase_cod,prfregbt_cod,prfrprbt_cod,prfrprphase_cod
                                                                  FROM &erp.gam_stdlig f
                                                                  WHERE f.stdent_cod=tf.stdent_cod AND f.stdent_ind=tf.stdent_ind
                                                                    AND f.codphase_no=tf.codphase_no )
 WHERE EXISTS (select 1 FROM &erp.gam_stdlig f
               WHERE f.stdent_cod=tf.stdent_cod AND f.stdent_ind=tf.stdent_ind
                 AND f.codphase_no=tf.codphase_no ) ;


 UPDATE se_gam_stdlig tf SET enreg_id=(select enreg_id FROM &erp.gam_stdlig f WHERE f.stdent_cod=tf.stdent_cod AND f.stdent_ind=tf.stdent_ind AND f.codphase_no=tf.codphase_no ) ;
 UPDATE se_gam_stdlig tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('stdent_cod@stdent_ind@codphase_no','GAM_STDLIG','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,codphase_no, prfregphase_cod, prfregbt_cod, stdent_cod, stdent_ind, opera_cod, operamod_cod, modulation_val, prepa_untps_cod, outil_cod, prfrprbt_cod, prfrprphase_cod, enreg_id, enreg_s, stdlig_rang, deb_dat, fin_dat, prepamach_tps, reamach_val, tps_coef, editcomment_ok, entrestock_ok, bons_nb
FROM (
  SELECT 'uv' source ,g.* FROM se_gam_stdlig g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gam_stdlig g

  )
GROUP BY codphase_no, prfregphase_cod, prfregbt_cod, stdent_cod, stdent_ind, opera_cod, operamod_cod, modulation_val, prepa_untps_cod, outil_cod, prfrprbt_cod, prfrprphase_cod, enreg_id, enreg_s, stdlig_rang, deb_dat, fin_dat, prepamach_tps, reamach_val, tps_coef, editcomment_ok, entrestock_ok, bons_nb
HAVING Count(1)!=2) r
ORDER BY  stdent_cod, stdent_ind ;